静态SQL

静态SQL是指SQL语法直接写在兼容PL/SQL语句中一个的特性。即把SQL语句直接写到程序里。写到变量里时，是动态SQL（见第6章）。除了特别说明，静态SQL和SQL语法相同。

静态SQL一般用于嵌入式SQL应用，在程序运行前，SQL语句必须是确定的。一般直接写在程序里。

游标

由系统构造和管理的游标是隐式游标，由用户构造和管理的游标为显式游标。隐式游标和显式游标都可以从他的属性获得会话游标信息。在PL/SQL中，通常显式游标是在第一次打开时解析它，而隐式游标在第一次执行SQL语句中解析他。

游标表达式用游标操作符表示，返回的是一个嵌套在查询语句中的游标。使用游标表达式的时候需要显示关闭，否则会出现锁表。

• 游标表达式把子查询作为外层查询的一列；

游标表达式把查询转换成一个结果集，该结果集可以作为参数传递给流或者转换函数。

显式游标

显式游标由用户构造和管理。必须声明显示游标，并给他命名和关联一个查询语句。显示游标不支持赋值，不能在表达式中使用，不能作为子程序的参数或宿主变量使用。如不得不使用，则选择使用游标变量来实现。

★声明显式游标

CURSOR cursor_name  [ RETURN return_type ] IS select_statement;

CREATE OR REPLACE PROCEDURE p_4_2_3_1 AS
  CURSOR c1 IS
    SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000;
  CURSOR c2 RETURN departments%ROWTYPE IS
    SELECT * FROM departments WHERE department_id = 110;
  CURSOR c3 IS
    SELECT * FROM departments WHERE department_id = 110;
BEGIN
  NULL;
END;
/

★Open and Close

声明显式游标之后，可以用OPEN语句打开，这个过程系统会分配数据库资源给此查询。处理查询：识别结果集，如果查询引用变量或游标参数，它的值会受影响。如果查询有FOR UPDATE子句，会锁住结果集。将游标定位在结果集的第一行之前。限制：同一个游标不能多次打开或关闭。只能打开一次关闭一次。

create table t1(id int,col varchar(30));
create table t2(id int,col varchar(30));
insert into t1 values(1,'a');
insert into t1 values(2,'b');

create or replace procedure p p_4_2_3_2 IS
  v1 int;
  v2 varchar(30);
  cursor c1 is select * from t1;
begin
  open c1;
  loop
    fetch c1 into v1,v2;
    exit when c1%notfound;
    insert into t2 values(v1,v2);
  end loop;
  close c1;
end;
/

★Fetch

打开一个显式游标后，能够用FETCH获得结果集中的每条数据。

FETCH cursor_name INTO into_clause

其中INTO_clause可以是变量列表或者是单个记录变量。对于查询返回的每一列，变量列表或记录变量必须有对应的兼容类型的变量。%TYPE和%TYPEROW对声明在FETHCH语句中的变量或记录变量非常有用。

FETCH语句获得结果集的当前行，将当前行的值存到变量或记录变量中，然后将游标下移到下一行。

通常在循环中使用FETCH语句，该循环当FETCH语句运行了所有的行后退出，为了检测退出条件，可以使用%NOFOUND属性。FETCH语句没有返回值时，PL/SQL不会引起异常。

CREATE OR REPLACE PROCEDURE p p_4_2_3_3_1 AS
  v_lastname employees.last_name%TYPE; -- variable for last_name
  v_jobid employees.job_id%TYPE; -- variable for job_id
  v_employees employees%ROWTYPE; -- record variable for row of table

  CURSOR c1 IS
    SELECT last_name, job_id FROM employees WHERE job_id='AD_PRES' ORDER BY last_name;
  CURSOR c2 IS
    SELECT * FROM employees WHERE job_id='AD_PRES' ORDER BY job_id;
BEGIN
  OPEN c1;
  LOOP -- Fetches 2 columns into variables
    FETCH c1 INTO v_lastname, v_jobid;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_lastname);
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );

  OPEN c2;
  LOOP -- Fetches entire row into the v_employees record
    FETCH c2 INTO v_employees;
    EXIT WHEN c2%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(v_employees.last_name);
  END LOOP;
  CLOSE c2;
END;
/
--Atkinson                 ST_CLERK
--Bell                     SH_CLERK
--Bissot                   ST_CLERK
--PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE p_4_2_3_3_2 AS
  CURSOR c IS
    SELECT e.job_id, j.job_title FROM employees e, jobs j
    WHERE e.job_id = j.job_id AND e.manager_id = 100 ORDER BY last_name;
  -- Record variables for rows of cursor result set:
  job1 c%ROWTYPE;
  job2 c%ROWTYPE;
  job3 c%ROWTYPE;
  job4 c%ROWTYPE;
  job5 c%ROWTYPE;
BEGIN
  OPEN c;
  FETCH c INTO job1; -- fetches first row
  FETCH c INTO job2; -- fetches second row
  FETCH c INTO job3; -- fetches third row
  FETCH c INTO job4; -- fetches fourth row
  FETCH c INTO job5; -- fetches fifth row
  CLOSE c;
  DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
  DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/
--Result:
--Sales Manager (SA_MAN)
--Administration Vice President (AD_VP)
--Sales Manager (SA_MAN)
--Stock Manager (ST_MAN)
--Marketing Manager (MK_MAN)
--PL/SQL procedure successfully completed.

★显式游标查询中的变量

与游标变量相关的查询可以引用相同作用域的任何变量。当用OPEN打开游标变量时，可以用查询中的任意变量来标识结果集，对于变量的后续修改，会修改结果集。

CREATE OR REPLACE PROCEDURE p p_4_2_3_4 AS
  sal employees.salary%TYPE;
  sal_multiple employees.salary%TYPE;
  factor INTEGER := 2;
  CURSOR c1 IS SELECT salary, salary*factor FROM employees WHERE job_id LIKE 'AD_%';
BEGIN
  OPEN c1; -- PL/SQL evaluates factor
  LOOP
    FETCH c1 INTO sal, sal_multiple;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1; -- Does not affect sal_multiple
  END LOOP;
  CLOSE c1;
END;
/
--Result:
--factor = 2
--sal = 4400
--sal_multiple = 8800
--factor = 3
--sal = 24000
--sal_multiple = 72000
--factor = 4
--sal = 17000
--sal_multiple = 68000
--factor = 5
--sal = 17000
--sal_multiple = 85000

★显式游标查询中的别名

查询操作需要游标FETCH到一个记录变量里，而记录变量必须使用%ROWTYPE，且投影列存在虚拟列（即表达式），则该该列必须有别名。且别名必须加AS, 否则报错。在ORACLE中可以加AS,也可以不加。

在程序中引用定义的虚拟列

CREATE OR REPLACE PROCEDURE p p_4_2_3_5 AS
  CURSOR c1 IS SELECT employee_id,(salary * .05) as raise FROM employees
    WHERE job_id LIKE '%_PRES' ORDER BY employee_id;
  emp_rec c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emp_rec;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE ('Raise for employee #' || emp_rec.employee_id ||' is $' || emp_rec.raise);
  END LOOP;
  CLOSE c1;
END;
/
--Result:
--Raise for employee #114 is $550
--Raise for employee #120 is $400
--Raise for employee #121 is $410
--Raise for employee #122 is $395
--Raise for employee #123 is $325
--Raise for employee #124 is $368.445
--Raise for employee #145 is $700
--Raise for employee #146 is $675
--Raise for employee #147 is $600
--Raise for employee #148 is $550
--Raise for employee #149 is $525
--Raise for employee #201 is $650

★显式游标属性

显式游标和游标变量具有相同的属性。

%ISOPEN：如果打开了游标则返回TRUE，否则返回FALSE。该属性用于：

• 在打开游标前用于检测显式游标是否打开。
• 在关闭游标前，检测是否已经打开。

CREATE OR REPLACE PROCEDURE p p_4_2_3_6_1 AS
  CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
  the_name employees.last_name%TYPE;
  the_salary employees.salary%TYPE;
BEGIN
  IF NOT c1%ISOPEN THEN
    OPEN c1;
  END IF;
  FETCH c1 INTO the_name, the_salary;
  IF c1%ISOPEN THEN
    CLOSE c1;
  END IF;
END;
/

%FOUND：用于判断是否有取回的行。返回值：

• NULL 在打开显式游标之后，FETCH数据之前。
• TRUE 最近一个FETCH获得了一条数据
• FALSE 其他

CREATE OR REPLACE PROCEDURE p_4_2_3_6_2 AS
  CURSOR c1 IS SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11 ORDER BY last_name;
  my_ename employees.last_name%TYPE;
  my_salary employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%FOUND THEN -- fetch succeeded
      DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
    ELSE -- fetch failed
      EXIT;
    END IF;
  END LOOP;
END;
/
--Result:
--Name = Austin, salary = 4800
--Name = De Haan, salary = 17000
--Name = Ernst, salary = 6000
--Name = Faviet, salary = 9000
--Name = Greenberg, salary = 12008
--Name = Hunold, salary = 9000
--Name = King, salary = 24000
--Name = Kochhar, salary = 17000
--Name = Lorentz, salary = 4200
--Name = Pataballa, salary = 4800

%NOTFOUND：用于退出循环

• NULL在打开显式游标之后，FETCH数据之前。
• FLASE最近一个FETCH获得了一条数据
• TRUE其他

CREATE OR REPLACE PROCEDURE p_4_2_3_6_3 AS
  CURSOR c1 IS  SELECT last_name, salary FROM employees
    WHERE ROWNUM < 11 ORDER BY last_name;
  my_ename employees.last_name%TYPE;
  my_salary employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO my_ename, my_salary;
    IF c1%NOTFOUND THEN -- fetch failed
      EXIT;
    ELSE -- fetch succeeded
    DBMS_OUTPUT.PUT_LINE('Name = '|| my_ename || ', salary = ' || my_salary);
    END IF;
  END LOOP;
END;
/
--Result:
--Name = Austin, salary = 4800
--Name = De Haan, salary = 17000
--Name = Ernst, salary = 6000
--Name = Faviet, salary = 9000
--Name = Greenberg, salary = 12008
--Name = Hunold, salary = 9000
--Name = King, salary = 24000
--Name = Kochhar, salary = 17000
--Name = Lorentz, salary = 4200
--Name = Pataballa, salary = 4800

%ROWCOUNT：返回值

• ZERO：在打开显式游标之后，FETCH数据之前
• 其他：FETCH获得了行数

CREATE OR REPLACE PROCEDURE p_4_2_3_6_4 AS
  CURSOR c1 IS  SELECT last_name FROM employees
    WHERE ROWNUM < 11 ORDER BY last_name;
  name employees.last_name%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO name;
    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
    DBMS_OUTPUT.PUT_LINE(  name);
    IF c1%ROWCOUNT = 5 THEN
      DBMS_OUTPUT.PUT_LINE('--- Fetched 5th row ---');
    END IF;
  END LOOP;
  CLOSE c1;
END;
/

隐式游标

隐式游标由PL/SQL构造和管理。PL/SQL会在每次运行SELECT和DML语句时打开隐式游标，隐式游标支持的属性如下：

• SQL%ISOPEN Attribute：游标是否打开
• SQL%FOUND Attribute：是否有任意行受影响
• SQL%NOTFOUND Attribute：有没有行受影响
• SQL%ROWCOUNT Attribute：有多少行受影响

★SQL%ISOPEN

始终返回FALSE，因为隐式游标在他的关联语句执行完后永远是关闭的。

★SQL%FOUND

返回值：

• NULL 没有SELECT或DML运行
• TRUE 如果SELECT或DML返回或影响了一行或多行
• FALSE 其他情况

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS SELECT * FROM departments;

CREATE OR REPLACE PROCEDURE p_4_2_1_2 (dept_no NUMBER) AS
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Delete succeeded for department number ' || dept_no);
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/
  call p(270);
  call p(400);

--Result:
--Delete succeeded for department number 270
--No department number 400

★SQL%NOTFOUND

与SQL%FOUND的返回值相反

• NULL 如果没有SELECT或DML运行
• FALSE 如果SELECT或DML返回或影响了一行或多行
• TRUE 其他

该属性对PL/SQL中的SELECT INTO语句不起作用，因为调用集合函数的SELECT INTO总会有返回值（有时会为0），所以SQL%NOTFOUND始终为FALSE。

DROP TABLE dept_temp;
CREATE TABLE dept_temp AS SELECT * FROM departments;

CREATE OR REPLACE PROCEDURE p_4_2_1_3 (dept_no NUMBER) AS
BEGIN
  DELETE FROM dept_temp
  WHERE department_id = dept_no;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Delete succeeded for department number ' || dept_no);
  ELSE
    DBMS_OUTPUT.PUT_LINE ('No department number ' || dept_no);
  END IF;
END;
/
  call p(270);
  call p(400);

--Result:
--Delete succeeded for department number 270
--No department number 400

★SQL%ROWCOUNT

返回值：

• NULL：如果没有SELECT或DML语句执行
• 其他值：SELECT或DML返回或影响的行数

没有BULK COLLECT子句的SELECT INTO语句返回多条记录时，SQL/ROWCOUNT的值为1，而不是真实的行数。

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS  SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE p_4_2_1_4 (dept_no NUMBER) AS
  mgr_no NUMBER(6) := 122;
BEGIN
  DELETE FROM employees_temp WHERE manager_id = mgr_no;

END;
/
--Result:
--Number of employees deleted: 8

处理结果集

游标可以用来处理查询结果集，可以用显式游标和隐式游标。前者只需用较少的代码，后者更为灵活。

下面的语句使用隐式游标

• 隐式游标FOR LOOP
• SELECT INTO

下面的语句使用显式游标

• 显式游标FOR LOOP
• OPEN、FETCH、CLOSE

SELECT INTO语句处理结果集

使用隐式游标，SELECT INTO 从一个或者多个数据库表获取数据（类似SQL的SELECT）然后把它们存储在变量中（ SQL的SELECT做不到）。

处理单行结果集：

如果希望查询只返回一行，可以使用SELECT INTO语句存储该行的值到一个或多个变量中或到一个记录变量中；如果返回可能是多行，而你只关心第n行，则可以使用where ROWNUM 来限制结果集。

DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS tb;
CREATE TABLE t(id INT, name VARCHAR(20));
CREATE TABLE tb(id INT, name VARCHAR(20));
INSERT INTO t VALUES(1, 'a');
INSERT INTO t VALUES(2, 'b');

CREATE OR REPLACE PROCEDURE p_4_3_1_1 AS
  b1 INT;
BEGIN
  SELECT ID INTO b1 FROM T WHERE ID=2;
  INSERT INTO TB VALUES(b1,'S');
end;
/

FOR LOOP语句处理结果集

游标FOR LOOP允许你执行一条SELECT语句，然后循环结果集的所有行。游标FOR LOOP即可应用于显式游标也可以用于隐式游标，游标变量除外。

如果只在游标FOR LOOP循环中使用SELECT语句，则可以在游标FOR LOOP内部指定SELECT语句。这种形式的游标FOR LOOP循环称为隐式游标FOR LOOP语句。如果在相同的PL/SQL块中多次引用SELECT语句，则需要为它定义显式游标并在游标FOR LOOP语句中指定该游标。这种形式的游标FOR LOOP循环称为显式游标FOR LOOP语句。

隐式游标FOR LOOP声明的隐式变量为返回类型为%ROWTYPE记录类型变量，该记录对循环来说是内部的，并只在循环执行期间存在。循环中的语句可以引用变量，同时只能通过别名来引用虚拟列。当声明完循环变量时，FOR LOOP打开游标，每次循环 FOR LOOP 都从结果集中获取一行存储在记录变量中，当没有行被获取时，游标FOR LOOP关闭游标。当循环中的语句控制权转移到外部或者PL/SQL产生异常时，游标也会关闭。

CREATE OR REPLACE PROCEDURE p_4_3_2_1 AS
BEGIN
  FOR item IN (
    SELECT last_name, job_id FROM employees
      WHERE job_id LIKE '%CLERK%' AND manager_id > 120
      ORDER BY last_name)
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/
--Result:
--Name = Atkinson, Job = ST_CLERK
--Name = Bell, Job = SH_CLERK
--Name = Bissot, Job = ST_CLERK
--...
--Name = Walsh, Job = SH_CLERK

显式 FOR LOOP Statement

CREATE OR REPLACE PROCEDURE p_4_3_2_2AS
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name;
BEGIN
  FOR item IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' ||item.job_id);
  END LOOP;
END;
/
--Result:
--Name = Atkinson, Job = ST_CLERK
--Name = Bell, Job = SH_CLERK
--Name = Bissot, Job = ST_CLERK
--...
--Name = Walsh, Job = SH_CLERK

OPEN-FETCH-CLOSE

为了完全控制查询结果集，需要声明显式游标并使用OPEN-FETCH-CLOSE并管理他们。这种处理数据集的技术非常复杂，但更加灵活。

使用多个游标，并行处理多个结果集；

在单次循环中处理多条数据，跳行或者将处理拆分成多个循环；

在一个PL/SQL块中指定查询，但在其他块中获得行。

注意：在游标变量和显式游标中都不可以用FETCH BULK COLLECT INTO

处理带有子查询的结果集

如果通过循环来处理结果集，并且对每一行都执行了另一个查询，则可以通过移除循环中的第二个查询，并使之成为第一个查询的子查询。普通的子查询对每个表进行评估，相关子查询对每一行进行评估。

CREATE PROCEDURE p_4_3_4_1 AS
  CURSOR c1 IS
  SELECT t1.department_id, department_name, staff
  FROM departments t1,
    ( SELECT department_id, COUNT(*) AS staff
      FROM employees
      GROUP BY department_id
    ) t2
  WHERE (t1.department_id = t2.department_id) AND staff >= 5
  ORDER BY staff;
BEGIN
  FOR dept IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE ('Department = ' || dept.department_name || ', staff = ' || dept.staff);
  END LOOP;
END;
/
--Result:
--Department = IT, staff = 5
--Department = Finance, staff = 6
--Department = Purchasing, staff = 6
--Department = Sales, staff = 34
--Department = Shipping, staff = 45

相关子查询

CREATE PROCEDURE p_4_3_4_2 AS
  CURSOR c1 IS
  SELECT department_id, last_name, salary
  FROM employees t
  WHERE salary > ( SELECT AVG(salary)
    FROM employees
    WHERE t.department_id = department_id
  )
  ORDER BY department_id, last_name;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
  END LOOP;
END;
/
--Result:
--Making above-average salary = Hartstein
--Making above-average salary = Raphaely
--Making above-average salary = Bell
--...
--Making above-average salary = Higgins

游标变量

一个游标变量非常像一个显式游标。但也有自己的特性

• 游标变量不限于一个查询
• 可以给游标变量赋值
• 可以在表达式中使用游标变量
• 可以作为子程序的参数，可以使用游标变量在子程序之间传递结果集。只限于SYS_REFCURSOR
• 不能接收参数，查询能够包括变量

★创建游标变量

创建游标变量：

• 定义REF CRUSOR类型，然后声明该类型的变量。
• 声明SYS_REFCURSOR的变量，一个游标变量被称为REF CURSOR(引用游标)

一个REF CURSOR类型基本的语句定义：

TYPE type_name IS REF CURSOR [ RETURN return_type ]

如果指定了return_type,则定义的引用游标变量和声明该类型的变量就是强类型，否则为弱类型。SYS_REFCURSOR类型和他的变量都为弱类型。对于强类型的游标变量，可以把返回指定类型的查询和他关联。对于弱类型的游标变量，可以与任何查询关联。弱类型的引用游标可以互相转换，也可以与SYS_REFCURSOR互相转换。可以将一个强类型的游标变量赋值给一个弱类型的游标变量；只有当2个强类型游标变量具有相同的类型，可以进行赋值。

在本次版本中 RETURN只是语法实现，所以游标类型实际都是弱游标。

CREATE OR REPLACE PROCEDURE p_4_4_1_1 AS
  TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong type
  TYPE genericcurtyp IS REF CURSOR; -- weak type
  cursor1 empcurtyp; -- strong cursor variable
  cursor2 genericcurtyp; -- weak cursor variable
  my_cursor SYS_REFCURSOR; -- weak cursor variable
  TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE; -- strong type
  dept_cv deptcurtyp; -- strong cursor variable
BEGIN
  NULL;
END;
/

CREATE OR REPLACE PROCEDURE p_4_4_1_2 AS
  TYPE EmpRecTyp IS RECORD (
    employee_id NUMBER,
    last_name VARCHAR2(25),
    salary NUMBER(8,2));
  TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
  emp_cv EmpCurTyp;
BEGIN
  NULL;
END;
/

★Open and Close

声明一个游标变量后，可以用OPEN FOR语句打开：

• 将游标变量和查询相互关联（通常查询返回多行），查询中可以包含绑定变量的占位符，它的值通过USING子句来指定。
• 处理查询：识别结果集。如果查询引用变量或游标参数，它的值会受影响。如果查询有FOR UPDATE子句，会锁住结果集。将游标定位在结果集的第一行之前。
• 使用其他的OPEN FOR再次打开游标变量之前，不需要关闭它。在重新打开游标变量后，与它关联的前一个查询会丢失。

注意

OPEN FOR 后跟的SQL语句必须加单引号

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT,col VARCHAR(10));
INSERT INTO t1 VALUES(123,'abc');
INSERT INTO t1 VALUES(456,'def');
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(col VARCHAR(10));
DROP PROCEDURE IF EXISTS p;

CREATE OR REPLACE PROCEDURE p_4_4_2 AS
  p_id INT:=456;
  v_col VARCHAR(10);
  TYPE t1CurTyp IS REF CURSOR;
  cursor_a t1CurTyp;
BEGIN
  OPEN cursor_a for 'SELECT col FROM t1 WHERE id=?' USING p_id;
  LOOP
    FETCH cursor_a INTO v_col;
    EXIT WHEN cursor_a%NOTFOUND;
    INSERT INTO t2 VALUES(v_col);
  END LOOP;
  CLOSE cursor_a;
END;
/
CALL p();
SELECT * FROM t2;

★Fetch

打开一个显式游标后，能够用FETCH获得结果集中的每条数据。游标变量的返回类型必须和FETCH语句的INTO子句相兼容。

注意

在游标变量和显式游标中都不可以用FETCH BULK COLLECT INTO

CREATE OR REPLACE PROCEDURE p_4_4_3 AS
  cv SYS_REFCURSOR; -- cursor variable
  v_lastname employees.last_name%TYPE; -- variable for last_name
  v_jobid employees.job_id%TYPE; -- variable for job_id
  v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
  OPEN cv FOR 'SELECT last_name, job_id FROM employees WHERE  job_id =	''AD_PRES'' ORDER BY last_name';
  LOOP -- Fetches 2 columns into variables
    FETCH cv INTO v_lastname, v_jobid;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(   v_jobid );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  OPEN cv FOR 'SELECT * FROM employees WHERE  employee_id = 121 ORDER BY last_name';
  LOOP -- Fetches entire row into the v_employees record
    FETCH cv INTO v_employees;
    EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_employees.job_id);
  END LOOP;
  CLOSE cv;
END;
/
--Result:
--Atkinson ST_CLERK
--Bell SH_CLERK
--Bissot ST_CLERK
--...
--Walsh SH_CLERK
-------------------------------------
--Higgins AC_MGR
--Greenberg FI_MGR
--Hartstein MK_MAN
--...
--Zlotkey SA_MAN

★游标变量赋值

可以使用另外的游标变量或者宿主变量给游标变量赋值。

target_cursor_variable := source_cursor_variable;

如果source游标变量是打开的，赋值后target游标变量也是打开的。两个游标变量指向同一区域。

CREATE OR REPLACE PROCEDURE p_4_4_4_1 AS
  cv1 SYS_REFCURSOR;
  cv2 SYS_REFCURSOR;
  v_lastname employees.last_name%TYPE; -- variable for last_name
  v_jobid employees.job_id%TYPE; -- variable for job_id
  v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
  OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE  job_id =	''AD_PRES'' ORDER BY last_name';
  cv2 := cv1 ;
  LOOP
    FETCH cv2 INTO v_lastname, v_jobid;
    EXIT WHEN cv2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(   v_jobid );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  CLOSE cv2;
END;
/

下面示例中，先把cv1赋值给cv2。再关闭游标变量cv1，当再次打开后，在oracle中，游标变量cv2从现象上来看依然与cv1保持一致（指向新的SQL语句）；本次版本在这种场景下，cv2会处于不确定状态，可能指向旧的游标上下文，也可能指向cv1新打开的游标上下文。要想再次引用cv2，只能给cv2重新赋值。

CREATE OR REPLACE PROCEDURE p_4_4_4_2 AS
  cv1 SYS_REFCURSOR;
  cv2 SYS_REFCURSOR;
  v_lastname employees.last_name%TYPE; -- variable for last_name
  v_jobid employees.job_id%TYPE; -- variable for job_id
  v_employees employees%ROWTYPE; -- record variable row of table
BEGIN
  OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE  job_id =	''AD_PRES'' ORDER BY last_name';
  cv2 := cv1 ;
  close cv1;
  OPEN cv1 FOR 'SELECT last_name, job_id FROM employees WHERE  job_id =	''AD_PRES'' ORDER BY last_name';
  LOOP
    FETCH cv2 INTO v_lastname, v_jobid;
    EXIT WHEN cv2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(   v_jobid );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( '-------------------------------------' );
  CLOSE cv1;
END;
/

★游标查询中的变量

与游标变量相关的查询可以引用他的作用域中的任意变量。当使用OPEN FOR打开游标变量时，PL/SQL会计算查询中的任意变量来标识结果集，对于变量的后续修改，不会改变结果集。若要更改结果集，必须更改变量的值，然后再次为同一查询打开游标变量。

CREATE OR REPLACE PROCEDURE p_4_4_5_1 AS
  sal employees.salary%TYPE;
  sal_multiple employees.salary%TYPE;
  factor INTEGER := 2;
  cv SYS_REFCURSOR;
BEGIN
  OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor ;
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
    factor := factor + 1; -- Does not affect sal_multiple
  END LOOP;
  CLOSE cv;
END;
/
--Result:
--factor = 2
--sal = 4400
--sal_multiple = 8800
--factor = 3
--sal = 24000
--sal_multiple = 48000
--factor = 4
--sal = 17000
--sal_multiple = 34000
--factor = 5
--sal = 17000
--sal_multiple = 34000

CREATE OR REPLACE PROCEDURE p_4_4_5_2 AS
  sal employees.salary%TYPE;
  sal_multiple employees.salary%TYPE;
  factor INTEGER := 2;
  cv SYS_REFCURSOR;
BEGIN
  DBMS_OUTPUT.PUT_LINE('factor = ' || factor);
  OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor;
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
  END LOOP;

  factor := factor + 1;

  DBMS_OUTPUT.PUT_LINE('factor = ' || factor);

  OPEN cv FOR 'SELECT salary, salary*? FROM employees WHERE job_id LIKE ''AD_PRES''' using factor;
  LOOP
    FETCH cv INTO sal, sal_multiple;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('sal = ' || sal);
    DBMS_OUTPUT.PUT_LINE('sal_multiple = ' || sal_multiple);
  END LOOP;
  CLOSE cv;
END;
/
--Result:
--factor = 2
--sal = 4400
--sal_multiple = 8800
--sal = 24000
--sal_multiple = 48000
--sal = 17000
--sal_multiple = 34000
--sal = 17000
--sal_multiple = 34000
--factor = 3
--sal = 4400
--sal_multiple = 13200
--sal = 24000
--sal_multiple = 72000
--sal = 17000
--sal_multiple = 51000
--sal = 17000
--sal_multiple = 51000

★游标变量的属性

游标变量和显式游标变量具有相同的属性。

★子函数的参数

游标变量可以作为子程序参数用于在子程序间传递结果集：可以在一个子程序中打开游标变量，在其他子程序中处理他。当声明一个游标变量作为子函数的参数时：

• 如果子程序打开或者给游标变量赋值，则参数需要为IN OUT类型；
• 如果子程序只是获取数据，关闭游标变量，则参数可以为IN 或 IN OUT类型;

注意

只有SYS_REFCURSOR游标变量支持。REF CURSOR不支持。

DROP TABLE IF EXISTS T2;
CREATE TABLE T2(ID INT,COL VARCHAR(10));
INSERT INTO T2 VALUES(111,'AAA');
INSERT INTO T2 VALUES(222,'BBB');
DROP TABLE IF EXISTS T3;
CREATE TABLE T3(ID INT,COL VARCHAR(10));

CREATE OR REPLACE PROCEDURE p_4_4_7_1 (CURSOR_A OUT SYS_REFCURSOR) IS
BEGIN
  OPEN CURSOR_A FOR 'SELECT * FROM t2';
END;
/
CREATE OR REPLACE PROCEDURE p_4_4_7_2 IS
  v1 INT;
  v2 VARCHAR(10);
  cursor_b SYS_REFCURSOR;
BEGIN
  pro2(cursor_b);
  LOOP
    FETCH cursor_b INTO v1,v2;
    EXIT WHEN cursor_b%NOTFOUND;
    INSERT INTO t3 VALUES(v1,v2);
  END LOOP;
END;
/

使用游标更新/删除数据

FOR UPDATE游标

在PLSQL内，当FOR UPDATE子句与游标类用法关联时，统称为FOR UPDATE 游标。FOR UPDATE 子句的相关使用详见《GBase 8s V8.8 SQL指南:语法.docx》。

可定义FOR UPDATE游标的游标类型包括隐式游标、显式游标、游标变量及FOR LOOP游标。

带有FOR UPDATE 子句的SELECT 游标，打开游标时，结果集中的行被锁定。当提交或回滚，游标关闭,被锁定的行解锁；由于隐式游标在语句运行完毕后保持关闭，所以FOR UPDATE应用于隐式游标总是被解锁的。缺省的锁等待为nowait。

语句定义：

--隐式游标
<select_statement> FOR UPDATE [OF column [,column]]
--显式游标
CURSOR <cursor_name> [ RETURN return_type ] IS <select_statement> FOR UPDATE [OF column [,column]]
--游标变量
OPEN <cursor_var_name> FOR <select_statement> FOR UPDATE [OF column [,column]]
--FOR LOOP 游标
FOR <record_var> IN (<select_statement> FOR UPDATE [OF column [,column]]
) LOOP

扩展子句WHERE CURRENT OF

可以使用游标更新或删除结果集中的数据。若需要使用游标更新或删除数据，则在游标关联的查询语句中一定要使用FOR UPDATE 选项。

当游标拨动到需要更新或删除的行时，就可以使用 UPDATE/DELETE 语句进行数据更新/删除。此时必须在 UPDATE/DELETE 语句结尾使用WHERE CURRENT OF 子句，以限定删除/更新游标当前所指的行。

语句定义：

--更新
<update_statement> WHERE CURRENT OF <cursor_name>
--删除
<delete_statement> WHERE CURRENT OF <cursor_name>

示例

使用游标更新表中数据

declare
v_name varchar(20);
cursor v_cursor is select name from employee for update;
begin
  open v_cursor;
  loop
    fetch v_cursor into v_name ;
    exit when v_cursor%notfound;
    update employee set job='job' where current of v_cursor;
  end loop;
  close v_cursor;
commit;
end;
/

使用游标删除表中数据

declare
v_name varchar(20);
cursor v_cursor is select name from employee where job='teacher' for update;
begin
  open v_cursor;
  loop
    fetch v_cursor into v_name ;
    exit when v_cursor%notfound;
    delete from employee where current of v_cursor;
  end loop;
  close v_cursor;
commit;
end;
/